OneStream Database Server Security and SQL Table Access

The Database Servers support Security properties which can be used to provision rights to access the content of database tables used in the OneStream Platform when using the Database Page and/or SQL Table Editor. Database Server Security is an essential configuration which should be considered when designing an environment and deploying a Solution Exchange solution that utilize the SQL Table Editor. Database Security can be configured on all databases within each OneStream environment.

  • Internal OneStream Database Server
  • External/Custom Database Server Connections

Database Server Security Properties provision table and view access using:

  • OneStream System / Tools Database Page: Provides comprehensive access to database information, allowing administrators, or provisioned users, to view and manage all of OneStream's database tables. This access is essential for managing stored data, debugging, and monitoring the health and performance of the system.
  • OneStream Dashboard Component SQL Table Editor: The SQL Table Editor is a Dashboard Component designed to display and interact with data from SQL tables within a dashboard. It provides a grid interface where users can view, filter, sort, and manipulate table data directly from the dashboard. It is unique as it is designed to validate the user’s access to content using the Ancillary Table security properties on the Database Server.

The Security properties allow provisioning of the database content to be segregated across users in the OneStream environment. For example,

  • Enable a “power user” population of users to access to the Database Page but restrict them from accessing sensitive data stored in a solution table.
  • Prevent the OneStream Administrator, or other groups, from viewing, or editing content presented using the SQL Table Editor in a Dashboard.

Database Server Security Roles

Members of the OneStream Administrators security group do not have default rights to the Database Server Security properties. All users should be provisioned by assigning OneStream Security Groups to Security Roles.

Access Group for Platform Tables

The Access Group for Platform Tables property have a default setting applied as the “Administrators” group and is applicable only to the OneStream Database Server tables. These are core tables that are part of the application's fundamental database architecture. The OneStream Database Server tables are presented as View only and cannot be edited using the SQL Table Editor. These tables are essential for the operation of the OneStream platform and include structures for metadata, transactional data, and system operations.

  • Application Database core Platform tables
  • System Database core Platform tables

Ancillary Tables

Ancillary tables are custom or supplemental tables that support additional business requirements, custom solutions, or Solution Exchange applications. Ancillary Tables are typically created or enabled by users or administrators.
For Ancillary Tables, consider the following:

  • They are not part of the core platform schema.

  • They can be stored in the Application Database or System Database.

  • Ancillary Table Security Roles have a default setting applied as the “Administrators” group.

When configuring Database Security Roles for External/Custom databases (for example, BIBlend), only the Ancillary Security Roles are applicable for provisioning access to the tables and views for the Database Page and SQL Table Editor.

Access Group for Ancillary Tables

The Access Group for Ancillary Tables enables users to view the content of Ancillary tables stored in the Application, System or External/Custom databases using the Database Page or via the SQL Table Editor in a Dashboard.

Maintenance Group for Ancillary Tables

The Maintenance Group for Ancillary Tables is required to enable the Allow Inserts, Allow Deletes and Allow Updates functionality found on the SQL Table Editor.

Table Creation Group for Ancillary Tables

The Table Creation Group for Ancillary Tables is required to enable the Create Table If Necessary functionality found in the SQL Table Editor.

Database Server Provisioning

Provision the Database Server connections using the OneStream Application Server Configuration file or using the System Configuration Page.

The System Configuration Page displays the security role assignments. By default, it displays the settings that are saved in the Application Server Configuration File. Settings from the configuration file are indicated by a filled (selected) check box.

System Configuration Page utilizing Application Server Configuration settings:

To override the Application Configuration File settings, deselect a security role and then enter a OneStream Security group in the text box.

System Configuration Page Override of Application Server Configuration settings:

The assignment of Groups to the Security Roles supports the use of table names, wildcard characters and contains characters in a comma delimited list of tables associated to valid Security Groups.

  • When the Database Page / Tools / Data Records utility is required, users must have access to all Platform Tables on the Access Group for Platform Tables property
  • Nested Groups must be used to provision Groups needing to share the same table access.
  • Syntax Examples: * = Security Group, Table* = Security Group, *Table* = Security Group

Syntax Examples

Security Group Assignment Syntax Results
AppAdmins “AppAdmins” Group provisioned to all tables
*=AppAdmins “AppAdmins” Group provisioned to access all tables
*=AppAdmins, Stage*=Integrators “AppAdmins” Group provisioned to access all tables. “Integrators” Group access restricted to table names starting with “Stage”

Database Page Behavior Restricted User Example:

The tables names are always visible in the Database Page, however the visibility of content on the Data tab will vary depending upon provisioning.

Ancillary Table Restriction of the Administrators Group – OneStream Database Server

OneStream Environments are preconfigured with selected Solution Exchange Solutions intended for OneStream Administrators which need to be maintained. Security designs intended to restrict members of the Administrators Group, or any other group from certain sets of Ancillary Tables, will be required to use nested groups, the delimited list and/or the wildcard method to maintain proper solution access.

Examples of Minimal Provisioning for Pre-Configured Solutions:

  • OneStream System Diagnostics (OSD): XFW_OSD* tables as XFW_OSD*=Administrators
  • OneStream Table Data Manager (TDM): XFW_TDM* tables as XFW_TDM*=Administrators
  • XFC* tables as XFC*=Administrators: CustomName* tables as Prefix*=Administrators

Example: Administrator Ancillary Table Access OSD/TDM:

Database Administration

Any users with Security provisioned to use the Database Administration feature found in the Database Page, used to Copy, Edit and Delete Tables and Views, will have unrestricted access to Platform and Ancillary Tables, overriding Database Server security properties.

  • Administer Application Database: Default=Nobody. This Application Security Role enables ability to update StageBIBlendInformation reference and Copy, Rename and Delete Application Database Tables and Views
  • Administer System Database: Default=Nobody. This System Security Role enables ability to Copy, Rename and Delete System Database Tables
  • Administer External Databases: Default=Nobody. This System Security Role enables ability to Copy, Rename and Delete External Database Tables

Provisioning Rights to the System Configuration Page

Access to the System Configuration Page should be restricted to full OneStream Administrators due to the nature of its ability to impact the OneStream environment.

  • Navigate to the System tab

  • Select System Security Roles

  • Provision full OneStream Administrator to System Configuration Page

    • System Security Roles – ManageSystemConfiguration

  • Provision non-full OneStream Administrators to System Configuration Page

    • System Security Roles – ManageSystemConfiguration, to enable editing

    • System User Interface Roles – SystemPane

    • System User Interface Roles – SystemConfigurationPage